﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.UI.WebControls.WebParts;

namespace oAuth2
{
    public class sqlHelper
    {
        private string connectstring = ConfigurationManager.ConnectionStrings["conn"].ToString();
        SqlConnection aSqlConnection;

        public sqlHelper()
        {
            aSqlConnection = new SqlConnection(connectstring);
            
        }

        public void InsertAccount(object Account)
        {
            aSqlConnection.Open();
            SqlCommand aSqlCommand = new SqlCommand();
            aSqlCommand.CommandType = CommandType.Text;
            aSqlCommand.Connection = aSqlConnection;
            if (Account is GoogleProfile)
            {
                try
                {
                    var aGoogleAccount = (GoogleProfile)Account;
                    string sql =
                        "insert into tbllinkedinConnections  (user_Email, FirstName, account_type) values (@user_Email, @FirstName, @account_type)";
                    aSqlCommand.CommandText = sql;
                    aSqlCommand.Parameters.Add(new SqlParameter("@user_Email", aGoogleAccount.Emails.Find(email => email.Type == "account").Value));
                    aSqlCommand.Parameters.Add(new SqlParameter("@FirstName", aGoogleAccount.DisplayName));
                    aSqlCommand.Parameters.Add(new SqlParameter("@account_type", "google"));
                    aSqlCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    if (!File.Exists("GoogleAccount.txt"))
                    {
                        File.Create("GoogleAccount.txt");
                    }
                    File.AppendText(ex.Message + "\n" + ex.StackTrace);
                }
               
            }
            if (Account is FaceBookUser)
            {
                try
                {
                    var aFacebookAccount = (FaceBookUser)Account;
                    string sql =
                       "insert into tbllinkedinConnections  (user_Email, FirstName, LastName, account_type) values (@user_Email, @FirstName, @LastName, @account_type)";
                    aSqlCommand.CommandText = sql;
                    aSqlCommand.Parameters.Add(new SqlParameter("@user_Email", aFacebookAccount.Email));
                    aSqlCommand.Parameters.Add(new SqlParameter("@FirstName", aFacebookAccount.first_name));
                    aSqlCommand.Parameters.Add(new SqlParameter("@LastName", aFacebookAccount.last_name));
                    aSqlCommand.Parameters.Add(new SqlParameter("@account_type", "facebook"));
                    aSqlCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    if (!File.Exists("FacebookAccount.txt"))
                    {
                        File.Create("FacebookAccount.txt");
                    }
                    File.AppendText(ex.Message + "\n" + ex.StackTrace);
                }
               
            }
            if (Account is LinkedInAccount)
            {
                try
                {
                    var aLinkedInAccount = (LinkedInAccount)Account;
                    string sql =
                       "insert into tbllinkedinConnections  (user_Email, FirstName, LastName, account_type) values (@user_Email, @FirstName, @LastName, @account_type)";
                    aSqlCommand.CommandText = sql;
                    aSqlCommand.CommandText = sql;
                    aSqlCommand.Parameters.Add(new SqlParameter("@user_Email", aLinkedInAccount.email_address));
                    aSqlCommand.Parameters.Add(new SqlParameter("@FirstName", aLinkedInAccount.fisrt_name));
                    aSqlCommand.Parameters.Add(new SqlParameter("@LastName", aLinkedInAccount.last_name));
                    aSqlCommand.Parameters.Add(new SqlParameter("@account_type", "linkedin"));
                    aSqlCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    if (!File.Exists("LinkedInAccount.txt"))
                    {
                        File.Create("LinkedInAccount.txt");
                    }
                    File.AppendText(ex.Message + "\n" + ex.StackTrace);
                }
               
            }
            aSqlConnection.Close();
        }

        public bool existAcount(string email, string account_type)
        {
            try
            {
                string sql = "select * from tbllinkedinConnections where user_Email ='" + email + "' and account_type='" + account_type + "'";
                aSqlConnection.Open();
                SqlCommand aSqlCommand = new SqlCommand();
                aSqlCommand.CommandType = CommandType.Text;
                aSqlCommand.Connection = aSqlConnection;
                aSqlCommand.CommandText = sql;
                SqlDataAdapter aSqlDataAdapter = new SqlDataAdapter(aSqlCommand);
                DataTable dt = new DataTable();
                aSqlDataAdapter.Fill(dt);
                aSqlConnection.Close();
                if (dt.Rows.Count != 0)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                //if (!File.Exists("LinkedInAccount.txt"))
                //{
                //    File.Create("LinkedInAccount.txt");
                //}
                //File.AppendText(ex.Message + "\n" + ex.StackTrace);
                
            }
            return false;
        }
    }
}